﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using WinNemoLib;
using System.Data.SqlClient;
using System.Text;
using System.Data;
using JQueryDataTables.Models;

namespace TireBias.Models
{
    public class WasteModel
    {
        public class Waste_DS {
            public string _code { get; set; }
            public string _desc { get; set; }
            public string _act { get; set; }
            public string _ModifyBy { get; set; }
            public string _ModifyDT { get; set; }
        }
        public class RES_WASTE_DS
        {
            public string _Biasno { get; set; }
            public string _Wsdate { get; set; }            
            public string _Reason { get; set; }
            public string _Qty { get; set; }
            public string _Empname { get; set; }
            public string _Shift { get; set; }
            public string _Topcode { get; set; }
            public string _Tcrcdlot { get; set; }
            public string _Toproll { get; set; }
         
        }
        public List<Waste_DS> SEL_WASTE_MASTER(string code,string desc ="")
        {
           
            string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();
            string wh = " where ";
            str.AppendLine(" select * from " + Nemo.GetINI("Dbase", "Base") + ".dbo.WASTE_REASON  ");
            if (StrUtil.IsNotEmpty(code))
            {
                str.Append(wh+ "  rs_code='" + code + "'");
                wh = " and ";
            }
            if (StrUtil.IsNotEmpty(desc))
            {
                str.Append(wh+ "  rs_desc='" + desc + "'");
            }

            SqlCommand cmd = new SqlCommand(str.ToString()+" order by convert(int,rs_code)" , cnn);
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;
        

            SqlDataAdapter my = new SqlDataAdapter();
            DataSet ds = new DataSet();
            my.SelectCommand = cmd;
            my.Fill(ds);
            List<Waste_DS> list = new List<Waste_DS>();
            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return list;
            }
            for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
            {
                DataRow items = ds.Tables[0].Rows[cuIndex];
                Waste_DS model = new Waste_DS();
                model._code = items["rs_code"].ToString();
                model._desc = items["rs_desc"].ToString();
                model._act = items["rs_act"].ToString();
                model._ModifyBy = items["ModifyBy"].ToString();
                model._ModifyDT = DateUtil.ToDisplayDateTime(items["ModifyDate"], DateUtil.eLanguage.EN);

                list.Add(model);
            }
            return list;
        }

        public DataTable SEL_WASTE_MASTER()
        {

            string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();

            str.AppendLine(" select RS_CODE,RS_DESC from " + Nemo.GetINI("Dbase", "Base") + ".dbo.WASTE_REASON  ");


            SqlCommand cmd = new SqlCommand(str.ToString() + " order by convert(int,rs_code)", cnn);
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;


            SqlDataAdapter my = new SqlDataAdapter();
            DataSet ds = new DataSet();
            my.SelectCommand = cmd;
            my.Fill(ds);
            List<Waste_DS> list = new List<Waste_DS>();
            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return new DataTable();
            }
            
            return ds.Tables[0];
        }

        public Boolean Ins_Master(Waste_DS incoming)
        {
            try
            {
                using (ConnectionWrapper cnn = Nemo.GetDefaultConfiguredDbConnectionWrapper())
                {
                    cnn.Open();
                    cnn.BeginTransaction();
                    StringBuilder comstr = new StringBuilder();
                    comstr.AppendLine(" INSERT INTO ");
                    comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[WASTE_REASON]");
                    comstr.AppendLine(" ([RS_CODE], [RS_DESC], [RS_ACT],[modifyby],Modifydate) ");
                    comstr.AppendLine("  VALUES (@code,@desc,@act, @empname,getdate())");


                    CommandWrapper cmd = cnn.CreateCommand();
                    cmd.CommandText = comstr.ToString();
                    cmd.AddParameterStringWithValue("@code", incoming._code);
                    cmd.AddParameterStringWithValue("@desc", incoming._desc);
                    cmd.AddParameterStringWithValue("@act", incoming._act);
                    cmd.AddParameterStringWithValue("@empname", incoming._ModifyBy);
           

                    if (cmd.ExecuteNonQuery() <= 0)
                    {
                        cnn.RollbackTransaction();
                        return false;
                    }
                    cnn.CommitTransaction();
                    return true;
                }

            }
            catch (Exception ex)
            {
                return false;
                Nemo.Error(ex);
            }

        }

        public Boolean UPD_Master(Waste_DS incoming)
        {
            try
            {
                using (ConnectionWrapper cnn = Nemo.GetDefaultConfiguredDbConnectionWrapper())
                {
                    cnn.Open();
                    cnn.BeginTransaction();
                    StringBuilder comstr = new StringBuilder();
                    comstr.AppendLine(" UPDATE ");
                    comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[WASTE_REASON]");
                    comstr.AppendLine(" SET [RS_DESC]=@desc, [RS_ACT]=@act,[modifyby]=@empname,Modifydate=getdate() ");
                    comstr.AppendLine(" Where [RS_CODE]=@code ");


                    CommandWrapper cmd = cnn.CreateCommand();
                    cmd.CommandText = comstr.ToString();
                    cmd.AddParameterStringWithValue("@code", incoming._code);
                    cmd.AddParameterStringWithValue("@desc", incoming._desc);
                    cmd.AddParameterStringWithValue("@act", incoming._act);
                    cmd.AddParameterStringWithValue("@empname", incoming._ModifyBy);


                    if (cmd.ExecuteNonQuery() <= 0)
                    {
                        cnn.RollbackTransaction();
                        return false;
                    }
                    cnn.CommitTransaction();
                    return true;
                }

            }
            catch (Exception ex)
            {
                return false;
                Nemo.Error(ex);
            }

        }

        public List<RES_WASTE_DS> SEL_WASTE(string dt,JQueryDataTableParamModel para,string shift ,string biasno,
            string Topcrdcd ="", string toplot="", string toproll="")
        {

            string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();

            str.AppendLine(" select Topcrdcd,Tcrcdlot,toproll,RS_CODE,sum(qty) qty from " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASWASTE  ");
            str.AppendLine(" where wsdate=convert(datetime,@dt,120)  ");


            if (StrUtil.IsNotEmpty(shift))
            {
                str.AppendLine(" and shift = @shift ");
            }
            if (StrUtil.IsNotEmpty(biasno))
            {
                str.AppendLine(" and biasno = @biasno ");
            }
            if (StrUtil.IsNotEmpty(Topcrdcd))
            {
                str.AppendLine(" and Topcrdcd = @Topcrdcd ");

            }
            if (StrUtil.IsNotEmpty(toplot))
            {
                str.AppendLine(" and Tcrcdlot = @lot ");

            }
            if (StrUtil.IsNotEmpty(toproll))
            {
                str.AppendLine(" and toproll = @roll ");

            }
            if (StrUtil.IsNotEmpty(para.sSearch))
            {
                str.AppendLine(" and RS_CODE like @code ");
            }
            SqlCommand cmd = new SqlCommand(str.ToString()+ " group by Topcrdcd,Tcrcdlot,toproll,RS_CODE ", cnn);
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@dt", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(dt, WinNemoLib.DateUtil.eLanguage.EN);
            if (StrUtil.IsNotEmpty(para.sSearch)) cmd.Parameters.Add("@code", SqlDbType.VarChar).Value = para.sSearch +"%";
            if (StrUtil.IsNotEmpty(shift)) cmd.Parameters.Add("@shift", SqlDbType.VarChar).Value = shift;
            if (StrUtil.IsNotEmpty(biasno)) cmd.Parameters.Add("@biasno", SqlDbType.VarChar).Value = biasno;
            if (StrUtil.IsNotEmpty(Topcrdcd)) cmd.Parameters.Add("@Topcrdcd", SqlDbType.VarChar).Value = Topcrdcd;
            if (StrUtil.IsNotEmpty(toproll)) cmd.Parameters.Add("@roll", SqlDbType.VarChar).Value = toproll;
            if (StrUtil.IsNotEmpty(toplot)) cmd.Parameters.Add("@lot", SqlDbType.VarChar).Value = toplot;

            SqlDataAdapter my = new SqlDataAdapter();
            DataSet ds = new DataSet();
            my.SelectCommand = cmd;
            my.Fill(ds);
            List<RES_WASTE_DS> list = new List<RES_WASTE_DS>();
            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return list;
            }
            for (int cuIndex = 0; cuIndex < ds.Tables[0].Rows.Count; cuIndex++)
            {
                DataRow items = ds.Tables[0].Rows[cuIndex];
                RES_WASTE_DS model = new RES_WASTE_DS();
                model._Topcode = items["Topcrdcd"].ToString();
                model._Tcrcdlot = items["Tcrcdlot"].ToString();
                model._Qty = items["qty"].ToString();
                model._Reason = items["RS_CODE"].ToString();
                model._Toproll = items["toproll"].ToString();
                list.Add(model);
            }
            return list;
        }

        public DataTable SEL_WASTE_REPORT(string dt,string shift,string lot,string roll,string biasno)
        {

            string ConnectionString = Nemo.GetConfiguredDbConnectionWrappertostring("Database");
            SqlConnection cnn = new SqlConnection(ConnectionString);
            StringBuilder str = new StringBuilder();

         //   str.AppendLine(" select a.RS_CODE,a.RS_DESC,isnull(sum(qty),0) qty, isnull(shift,@shift) shift, isnull(wsdate,convert(datetime,@dt,120)) wsdate from " + Nemo.GetINI("Dbase", "Base") + ".dbo.WASTE_REASON a LEFT JOIN ( SELECT  * ");
            str.Append("select  a.RS_CODE,b.RS_DESC,isnull(sum(qty),0) qty,  shift,wsdate ");
            str.AppendLine(" from " + Nemo.GetINI("Dbase", "Base") + ".dbo.BIASWASTE  a ");
            str.AppendLine(" INNER JOIN  " + Nemo.GetINI("Dbase", "Base") + ".dbo.WASTE_REASON  b on a.rs_code=b.rs_code  ");
            str.AppendLine(" where b.rs_act ='X' ");
            str.AppendLine(" and wsdate =convert(datetime,@dt,120) and shift=@shift ");
            if (StrUtil.IsNotEmpty(lot)) str.AppendLine(" and tcrcdlot=@lot ");
            if (StrUtil.IsNotEmpty(roll)) str.AppendLine(" and toproll=@roll ");
            if (StrUtil.IsNotEmpty(biasno)) str.AppendLine(" and biasno=@biasno ");
            str.AppendLine(" group by a.RS_CODE,b.RS_DESC,shift,wsdate ");
            //str.AppendLine(") b on a.RS_CODE=b.RS_CODE where a.rs_act='X' group by a.RS_CODE,a.RS_DESC,shift,wsdate");

            SqlCommand cmd = new SqlCommand(str.ToString() +" order by convert(int,a.rs_code)", cnn);
            cmd.Connection.Open();
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@dt", SqlDbType.VarChar).Value = DateUtil.ToInternalDate(dt, WinNemoLib.DateUtil.eLanguage.EN);
            if (StrUtil.IsNotEmpty(roll)) cmd.Parameters.Add("@biasno", SqlDbType.VarChar).Value = biasno;

            if (StrUtil.IsNotEmpty(roll)) cmd.Parameters.Add("@roll", SqlDbType.VarChar).Value = roll;
            if (StrUtil.IsNotEmpty(lot)) cmd.Parameters.Add("@lot", SqlDbType.VarChar).Value = lot;
            if (StrUtil.IsNotEmpty(shift)) cmd.Parameters.Add("@shift", SqlDbType.VarChar).Value = shift;

            SqlDataAdapter my = new SqlDataAdapter();
            DataSet ds = new DataSet();
            my.SelectCommand = cmd;
            my.Fill(ds);
        
            if (ds.Tables[0].Rows.Count <= 0)
            {
                cmd.Connection.Close();
                return new DataTable();
            }
            return ds.Tables[0];

        }

        public Boolean Ins_Data(RES_WASTE_DS incoming)
        {
            try
            {
                using (ConnectionWrapper cnn = Nemo.GetDefaultConfiguredDbConnectionWrapper())
                {
                    cnn.Open();
                    cnn.BeginTransaction();
                    StringBuilder comstr = new StringBuilder();
                    comstr.AppendLine(" INSERT INTO ");
                    comstr.AppendLine(Nemo.GetINI("Dbase", "Base") + ".[dbo].[BIASWASTE]");
                    comstr.AppendLine(" ([BIASNO], [wsdate], [Topcrdcd],Tcrcdlot,Toproll, [RS_CODE],[qty] ,[modifyby],Modifydate,shift) ");
                    comstr.AppendLine("  VALUES (@topno,convert(datetime, @wsdate,120), @topcode,@Tcrcdlot,@toproll, @reason,@qty, @empname,getdate(),@shift)");


                    CommandWrapper cmd = cnn.CreateCommand();
                    cmd.CommandText = comstr.ToString();



                    cmd.AddParameterStringWithValue("@wsdate", DateUtil.ToInternalDate(incoming._Wsdate, WinNemoLib.DateUtil.eLanguage.EN));
                    cmd.AddParameterStringWithValue("@topno", incoming._Biasno);                    
                    cmd.AddParameterStringWithValue("@qty", incoming._Qty.Replace(",", ""));
                    cmd.AddParameterStringWithValue("@reason", incoming._Reason);
                    cmd.AddParameterStringWithValue("@empname", incoming._Empname);
                    cmd.AddParameterStringWithValue("@shift", incoming._Shift);
                       cmd.AddParameterStringWithValue("@topcode", incoming._Topcode);
                       cmd.AddParameterStringWithValue("@Tcrcdlot", incoming._Tcrcdlot);
                       cmd.AddParameterStringWithValue("@toproll", incoming._Toproll);

                    if (cmd.ExecuteNonQuery() <= 0)
                    {
                        cnn.RollbackTransaction();
                        return false;
                    }
                    cnn.CommitTransaction();
                    return true;
                }

            }
            catch (Exception ex)
            {
                return false;
                Nemo.Error(ex);
            }

        }
    }
}